# /usr/bin/perl

use DBI;

# db に接続
$data_source = 'DBI:Pg:dbname=MarketDatabase';
$user_name = 'jiro';
$db = DBI->connect( $data_source, $user_name ) || die $db->errstr;

# 為替コードを取得
$sql = "select '998407';";
#$sql = "select code from currency_exchange_type_ja_table;";
$st = $db->prepare( $sql ) || die $db->errstr;
$st->execute() || die $db->errstr;
while ( $code = $st->fetchrow_array() ) {
	push( @currency_codes, $code );
}
$st->finish();

# 各為替についてスコアの高い順に１０件を抽出しリンク文字列を構築
for ( $i = 0; $i < 2; $i++ ) {

	foreach $currency_code ( @currency_codes ) {

#		print $currency_code . "\n";

		#　通貨名を取得
		$sql = "select target_currency_code from currency_exchange_type_ja_table where code = '$currency_code';";
		$st = $db->prepare( $sql ) || die print( $db->errstr . $! );
		$st->execute() || die $db->strerr;
		$target_code = $st->fetchrow_array();
		$st->finish();
		
		$sql = "select japanese_name from currency_type_table where code = '$target_code';";
#		print $sql;
		$st = $db->prepare( $sql ) || die print( $db->errstr . $! );
		$st->execute() || die $db->strerr;
		$currency_name = $st->fetchrow_array();
		$st->finish();

		if ( $i == 0 ) {
			$is_desc = "desc";
		} else {
			$is_desc = "";
		}

		$sql = "select code from analyze_correlation_score_table where reference_code = '$currency_code' ";
		$sql .= "order by score $is_desc limit 10;";
#		print $sql . "\n";
		$st = $db->prepare( $sql ) || die print( $db->errstr . $! );
		$st->execute() || die $db->strerr;
		while ( $code = $st->fetchrow_array() ) {
			push( @codes, $code );
		}
		$st->finish();

		if ( $i == 0 ) {
			print $currency_name . "と一緒に変動する銘柄\n";
		} else { 
			print $currency_name . "と反対に変動する銘柄\n";
		}

		if ( $#codes == -1 ) {
			next;
		}

		foreach $code ( @codes ) {

			if ( $code == "" ) {
				next;
			}
			
			# 企業名を取得
			$sql = "select name from stock_code_ja_table where code = '$code';";
			$st = $db->prepare( $sql ) || die print( $db->errstr . $! );
			$st->execute() || die $db->strerr;
			$name = $st->fetchrow_array();
			$st->finish();

			# はてなブログ用にリンクを構築し出力
#			$natena_link = "[http://stocks.finance.yahoo.co.jp/stocks/detail/?code=$code:title=【$code】$name]";
			$natena_link = "[http://company.nikkei.co.jp/index.aspx?scode=$code:title=【$code】$name]";
			print $natena_link . "\n";

		}
		
		@codes = undef;
	}
	print "\n";
}
